﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Resources;
using System.Reflection;

//Service for user use
public class UserService
{
    
    //initialize a user type of User
    private User uUser;
    //initializes a connection to MySQL database
    static MySqlConnection conn;
    //initializes a HAInfoService
    HAInfoService HAI = new HAInfoService();
    //initializes a MySqlDataAdapter
    static MySqlDataAdapter sqlAdap;
    //Represents an in-memory cache of data.
    static DataSet DS = new DataSet();
    //variable contains select command
    String strSelectCommand;
    //Contructor
    public UserService()
    {
        //Update DB
        UpdateDataBase();
    }
    /// <summary>
    /// 
    /// </summary>
    /// <returns>connection string</returns>
    static MySqlConnection MySQLConnection()
    {
        //initialize a connection to MySQL DB
        MySqlConnection mysqlCon;
        //temp string to hold connection string from web.config
        string strProvider = GetConnStr();
        try
        {
            //initializes mySQLConnection with above string
            mysqlCon = new MySqlConnection(strProvider);

        }
        catch
        {
            //assign null to connection
            return null;
        }
        //return right one
        return mysqlCon;
    }
    //Get connection string from file Web.config
    static String GetConnStr()
    {
        //temp string
        String strConn = "";
        //assign string that been got from web.config to tmp string
        //strConn = ConfigurationManager.AppSettings[Resources.Resources.ConnectionString];
        strConn = ConfigurationManager.AppSettings["ConnectionString"];
        //return connection string
        return strConn;
    }
    
    /// <summary>
    /// contructor with parameter 
    /// </summary>
    /// <param name="user"></param>
    public UserService(User user)
    {
        //assign parameter to variable 'user' 
        this.uUser = user;
        //Update Database
        UpdateDataBase();

    }
    /// <summary>
    /// return DS, dataset
    /// </summary>
    /// <returns></returns>
    public DataSet GetDataset()
    {
        return DS;
    }
    /// <summary>
    /// Insert to database with parameters: ID, timelog, MAC
    /// </summary>
    /// <returns></returns>
    public bool InsertNewRow()
    {
        return InsertRow(uUser.GetID(), uUser.GetTimelog(), uUser.GetMAC(), "", "");

    }
    /// <summary>
    /// update database
    /// </summary>
    public void UpdateDataBase()
    {
        //temp string to hold select command
        strSelectCommand = "SELECT * FROM persondailylog";
        //assing connection to conn Connection, use after
        conn = MySQLConnection();
        //Assign 
        sqlAdap = new MySqlDataAdapter(strSelectCommand, conn);
        DS.Clear();;
        //sqlAdap.FillSchema(DS, SchemaType.Source, "persondailylog");
        sqlAdap.Fill(DS, "persondailylog");
    }
    /// <summary>
    /// 
    /// </summary>
    /// <returns>true if user's checked else return false</returns>
    public bool IsUserChecked()
    {
        //assign each row (item)of table from DS to array of datarow 
        DataRow[] rows = DS.Tables[0].Select("PersonId = '" + uUser.GetID() + "'");
        //if DS has value
        if (rows != null)
        {
            //browse all rows
            for (int i = 0; i < rows.Length; i++)
            {
                //get time of record
                DateTime dtmDay = (DateTime)rows[i][3];
                //if date to check is equal to date in record
                if (dtmDay.Year == DateTime.Now.Year &&
                    dtmDay.Month == DateTime.Now.Month &&
                    dtmDay.Day == DateTime.Now.Day)
                    //return user's checked and exit
                    return true;
            }
            //else return fall
            return false;
        }
        //not checked
        return false;

    }   
    /// <summary>
    /// return true if Update successful else return false
    /// </summary>
    /// <param name="lngUserID"></param>
    /// <param name="dtmDay"></param>
    /// <param name="strMAC"></param>
    /// <param name="strNote"></param>
    /// <param name="strUpDate"></param>
    /// <returns></returns>
    public bool UpdateUserInfoInDay(long lngUserID, DateTime dtmDay, String strMAC, String strNote, String strUpDate)
    {
        //hold values
        long logRowID = -1;
        //    
        // Summary:
        //     Gets an array of all DataRow objects that match the filter criteria
        //     in order of primary key (or lacking one, order of addition.)
        // Returns:
        //     An array of System.Data.DataRow objects.
        //DS in database is table "PersonDailyLog"
        DataRow[] rows = DS.Tables[0].Select("personid = '" + lngUserID + "'");
        //browse all rows  
        foreach (DataRow r in rows)
        {   //tmp variable for a datetime type
            DateTime dtmLogDate;
            //     Converts the specified string representation of a date and time to its System.DateTime
            //     equivalent and returns a value that indicates whether the conversion succeeded.
            //     if converting successsful!
            if (DateTime.TryParse(r["timelog"].ToString(), out dtmLogDate))
            {
                //if the date in dataset = inputted date 
                if (dtmLogDate.DayOfYear == dtmDay.DayOfYear && dtmLogDate.Year == dtmDay.Year)
                {
                    //assign rowID (of course it greate than 0) 
                    logRowID = long.Parse(r[0].ToString());
                }
            }   
        }
        //if row > 0, Just update DB with that parameters
        if (logRowID > 0)
        {
            //update string
            String updateStr = "UPDATE persondailylog SET macaddress = @mac, Note = @note, LastModifiedBy = @updatetor, LastModifiedDate = @updatedate WHERE Id = @id ";
            //Create a object of MySQLCommand to updating DB
            MySqlCommand UpdateCM = new MySqlCommand(updateStr, conn);
            //Update every parameter into DB
            UpdateCM.Parameters.Add("@mac", MySqlDbType.VarChar, 20).Value = strMAC;
            UpdateCM.Parameters.Add("@note", MySqlDbType.VarChar, 255).Value = strNote;
            //Lastdate modified
            UpdateCM.Parameters.Add("@updatetor", MySqlDbType.VarChar).Value = strUpDate;
            UpdateCM.Parameters.Add("@updatedate", MySqlDbType.DateTime).Value = DateTime.Now; ;
            UpdateCM.Parameters.Add("@id", MySqlDbType.Int32).Value = logRowID;
            //Do
            try
            {
                //Open MySqlConnection
                conn.Open();
                //Execute query
                UpdateCM.ExecuteNonQuery();

            }
            //trap errors
            catch
            {
                //Mark that update fail
                return false;
            }
            finally
            {
                //Whatever, close connection
                conn.Close();
            }
        }
        //if rowID < 0, Do an inserting
        else
        {
            //Try to insert a new row into DB with above parameters
            //if success, return true
            return InsertRow(lngUserID, dtmDay, strMAC, strNote, strUpDate);

        }
        //Update successful
        return true;
    }
    /// <summary>
    /// return true if insert row success else return false
    /// </summary>
    /// <param name="logUserID"></param>
    /// <param name="dtmDay"></param>
    /// <param name="strMAC"></param>
    /// <param name="strNote"></param>
    /// <param name="strCreator"></param>
    /// <returns></returns>
    /// 
    public bool InsertRow(long logUserID, DateTime dtmDay, String strMAC, String strNote, String strCreator)
    {
        //tmp variable type of Boolean
        //result
        bool blnResult = true;
        //Create an MySqlCommand object to do an inserting
        //with query and Connection string
        MySqlCommand InserCMStr = new MySqlCommand(
        "INSERT INTO persondailylog (PersonId,macaddress,timelog, note, CreatedBy, CreatedDate) " +
        "VALUES ( @PersonId, @MACAddress, @TimeLog, @note, @Creator, @creatdate)", conn);
        InserCMStr.Parameters.Add("@PersonId", MySqlDbType.UInt32, 11, "PersonId").Value = logUserID;
        InserCMStr.Parameters.Add("@MACAddress", MySqlDbType.VarChar, 20, "MACAddress").Value = strMAC;
        InserCMStr.Parameters.Add("@TimeLog", MySqlDbType.DateTime).Value = dtmDay;
        InserCMStr.Parameters.Add("@note", MySqlDbType.VarChar, 255).Value = strNote;
        InserCMStr.Parameters.Add("@Creator", MySqlDbType.VarChar, 40).Value = strCreator;
        InserCMStr.Parameters.Add("@creatdate", MySqlDbType.DateTime).Value = DateTime.Now;
        //Do
        try
        {
            //Open connection
            conn.Open();
            //Execute query
            InserCMStr.ExecuteNonQuery();
        }
        //trap errors
        catch
        {
            //result 
            blnResult = false;
        }
        finally
        {
            //Whatever, close connection
            conn.Close();
        }
        //return result
        return blnResult;
    }
}
